---
title: "Mysql Join Next Row"
date: 2019-5-22
categories:
- mysql
tags:
---

<div id="content">
<blockquote>
<p>
<a href="https://forums.mysql.com/read.php?10,295387,295412#msg-295412">https://forums.mysql.com/read.php?10,295387,295412#msg-295412</a>
</p>
</blockquote>
<div class="org-src-container">
<pre class="src src-sql"><span style="font-weight: bold;">drop</span> <span style="font-weight: bold;">table</span> <span style="font-weight: bold;">if</span> <span style="font-weight: bold;">exists</span> t;
<span style="font-weight: bold;">create</span> <span style="font-weight: bold;">table</span> <span style="font-weight: bold;">t</span>(d <span style="font-weight: bold; text-decoration: underline;">timestamp</span>,i <span style="font-weight: bold; text-decoration: underline;">int</span>);
<span style="font-weight: bold;">insert</span> <span style="font-weight: bold;">into</span> t <span style="font-weight: bold;">values</span>
(<span style="font-style: italic;">'2009-12-1 00:00:00'</span>,1),(<span style="font-style: italic;">'2009-12-3 00:00:00'</span>,3),(<span style="font-style: italic;">'2009-12-5 00:00:00'</span>,5),(<span style="font-style: italic;">'2009-12-8 00:00:00'</span>,8);

<span style="font-weight: bold;">select</span> x.*
<span style="font-weight: bold;">from</span> (
  <span style="font-weight: bold;">select</span> a.d <span style="font-weight: bold;">as</span> thisdate, a.i <span style="font-weight: bold;">as</span> thisvalue, b.d <span style="font-weight: bold;">as</span> nextdate, b.i <span style="font-weight: bold;">as</span> nextvalue
  <span style="font-weight: bold;">from</span> t a
  <span style="font-weight: bold;">join</span> t b <span style="font-weight: bold;">on</span> a.d &lt; b.d
) x
<span style="font-weight: bold;">left</span> <span style="font-weight: bold;">join</span> (
  <span style="font-weight: bold;">select</span> a.d <span style="font-weight: bold;">as</span> thisdate, b.d <span style="font-weight: bold;">as</span> nextdate
  <span style="font-weight: bold;">from</span> t a
  <span style="font-weight: bold;">join</span> t b <span style="font-weight: bold;">on</span> a.d &lt; b.d
) y <span style="font-weight: bold;">on</span> x.thisdate = y.thisdate <span style="font-weight: bold;">and</span> x.nextdate &gt; y.nextdate
<span style="font-weight: bold;">where</span> y.nextdate <span style="font-weight: bold;">is</span> <span style="font-weight: bold;">null</span>
<span style="font-weight: bold;">order</span> <span style="font-weight: bold;">by</span> x.thisdate, x.nextdate; 
+<span style="font-weight: bold; font-style: italic;">---------------------+-----------+---------------------+-----------+</span>
| thisdate            | thisvalue | nextdate            | nextvalue |
+<span style="font-weight: bold; font-style: italic;">---------------------+-----------+---------------------+-----------+</span>
| 2009-12-01 00:00:00 |         1 | 2009-12-03 00:00:00 |         3 |
| 2009-12-03 00:00:00 |         3 | 2009-12-05 00:00:00 |         5 |
| 2009-12-05 00:00:00 |         5 | 2009-12-08 00:00:00 |         8 |
+<span style="font-weight: bold; font-style: italic;">---------------------+-----------+---------------------+-----------+</span>
</pre>
</div>
</div>
<div class="status" id="postamble">
<p class="date">Date: 2019-5-22</p>
<p class="author">Author: gdme1320</p>
<p class="validation"><a href="http://validator.w3.org/check?uri=referer">Validate</a></p>
</div>
